package com.rx.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.rx.common.DBUtils;
import com.rx.entity.Pictures;

/**
 * 
 * @类名: PicturesDaoImpl
 * @描述: 图片上传Dao类
 * @作者：侯建军
 * @日期：2018年6月26日 上午8:30:58
 *
 */
public class PicturesDaoImpl extends PicturesDaoAbstract<Pictures> {

	@Override
	public boolean insert(Pictures pictures) throws Exception {
		Connection conn = DBUtils.getConnection();

		String sql = "INSERT INTO  pictures (picType,goodsId,picUrl,picDesc,createDate) VALUES(?,?,?,?,?)";
		// 创建PreparedStatement
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, pictures.getPicType());
		ps.setInt(2, pictures.getGoodsId());
		ps.setString(3, pictures.getPicUrl());
		ps.setString(4, pictures.getPicDesc());
		ps.setDate(5, new Date(new java.util.Date().getTime()));
		// 执行语句
		if (ps.executeUpdate() > 0) {
			return true;
		}
		DBUtils.close(conn, ps, null);
		return false;
	}

	@Override
	public boolean update(Pictures pictures) throws Exception {
		Connection conn = DBUtils.getConnection();

		String sql = "UPDATE pictures SET picType=?,goodsId=?,picUrl=?,picDesc=?,createDate=? WHERE id=? ";
		// 创建PreparedStatement
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, pictures.getPicType());
		ps.setInt(2, pictures.getGoodsId());
		ps.setString(3, pictures.getPicUrl());
		ps.setString(4, pictures.getPicDesc());
		ps.setDate(5, new Date(new java.util.Date().getTime()));
		ps.setInt(6, pictures.getId());
		// 执行语句
		if (ps.executeUpdate() > 0) {
			return true;
		}
		DBUtils.close(conn, ps, null);
		return false;
	}

	@Override
	public boolean delete(int id) throws Exception {
		Connection conn = DBUtils.getConnection();
		String sql = "DELETE FROM pictures WHERE id=?";
		// 创建PreparedStatement
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		if (ps.executeUpdate() > 0) {
			return true;
		}
		DBUtils.close(conn, ps, null);
		return false;
	}

	@Override
	public List<Pictures> selectAll() throws Exception {
		Connection conn = DBUtils.getConnection();
		String sql = "SELECT id,picType,goodsId,picUrl,picDesc,createDate FROM pictures ";
		PreparedStatement ps = conn.prepareStatement(sql);
		// 将数据库中的记录转换为对象
		List<Pictures> list = new ArrayList<>();
		// 结果集
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			Pictures pictures = new Pictures();
			pictures.setId(rs.getInt(1));
			pictures.setPicType(rs.getInt(2));
			pictures.setGoodsId(rs.getInt(3));
			pictures.setPicUrl(rs.getString(4));
			pictures.setPicDesc(rs.getString(5));
			pictures.setCreateDate(rs.getTimestamp(6));
			list.add(pictures);
		}
		DBUtils.close(conn, ps, rs);
		return list;
	}

	@Override
	public List<Pictures> selectWhere(String whereSql) throws Exception {
		Connection conn = DBUtils.getConnection();
		String sql = "SELECT id,picType,goodsId,picUrl,picDesc,createDate FROM pictures WHERE 1=1 " + whereSql;
		// 创建PreparedStatement
		PreparedStatement ps = conn.prepareStatement(sql);
		// 将数据库中的记录转换为对象
		List<Pictures> list = new ArrayList<>();
		// 结果集
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			Pictures pictures = new Pictures();
			pictures.setId(rs.getInt(1));
			pictures.setPicType(rs.getInt(2));
			pictures.setGoodsId(rs.getInt(3));
			pictures.setPicUrl(rs.getString(4));
			pictures.setPicDesc(rs.getString(5));
			pictures.setCreateDate(rs.getTimestamp(6));
			list.add(pictures);
		}
		DBUtils.close(conn, ps, rs);
		return list;
	}

	@Override
	public Map<String, Object> selectPage(String whereSql, int currentPage, int pageSize) throws Exception {
		Connection conn = DBUtils.getConnection();
		int index = (currentPage - 1) * pageSize;
		String sql = "SELECT id,picType,goodsId,picUrl,picDesc,createDate FROM pictures WHERE 1=1 " + whereSql
				+ "ORDER BY id desc LIMIT " + index + "," + pageSize;
		PreparedStatement ps0 = conn.prepareStatement(sql);
		ResultSet rs0 = ps0.executeQuery();
		List<Pictures> list = new ArrayList<>();
		while (rs0.next()) {
			Pictures pictures = new Pictures();
			pictures.setId(rs0.getInt(1));
			pictures.setPicType(rs0.getInt(2));
			pictures.setGoodsId(rs0.getInt(3));
			pictures.setPicUrl(rs0.getString(4));
			pictures.setPicDesc(rs0.getString(5));
			pictures.setCreateDate(rs0.getTimestamp(6));
			list.add(pictures);
		}

		sql = "SELECT count(*) FROM pictures  WHERE 1=1 " + whereSql;
		PreparedStatement ps1 = conn.prepareStatement(sql);
		ResultSet rs1 = ps1.executeQuery();
		int count = 0;
		int pageNum = 0;
		if (rs1.next()) {
			count = rs1.getInt(1);
		}

		Map<String, Object> map = new HashMap<>();

		if (count % pageSize == 0) {
			pageNum = count / pageSize;
		} else {
			pageNum = count / pageSize + 1;
		}

		map.put("list", list);
		map.put("count", count);
		map.put("pageNum", pageNum);
		// 关闭连接
		DBUtils.close(conn, ps0, rs0);
		DBUtils.close(conn, ps1, rs1);
		return map;
	}

}
